unit MMDBUpdaterMain;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.Win.ADODB, Data.DB, Midas,
  Datasnap.DBClient, MMDBEngine, Vcl.StdCtrls, MMDBRegistry, Vcl.ExtCtrls,
  Data.FMTBcd, Data.SqlExpr, DBAccess, Ora, MemDS, OraCall, Vcl.DBCGrids, Vcl.Grids, Vcl.DBGrids, CRGrid;

type
  TMMDBFrm = class(TForm)
    dbDataSet: TClientDataSet;
    ADOConnection: TADOConnection;
    ADOQuery: TADOQuery;
    Memo1: TMemo;
    btnUpdate: TButton;
    edDocCtrlPath: TEdit;
    Label1: TLabel;
    edKitsPath: TEdit;
    Label2: TLabel;
    edNetCDSPath: TEdit;
    Label3: TLabel;
    edLocalCDSPath: TEdit;
    Label4: TLabel;
    Label5: TLabel;
    edLogFilePath: TEdit;
    btnPathUpdate: TButton;
    Timer1: TTimer;
    lblCountDown: TLabel;
    OraQuery: TOraQuery;
    OraSession: TOraSession;
    dsDocList: TClientDataSet;
    CRDBGrid1: TCRDBGrid;
    DataSource1: TDataSource;
    procedure UpdateDocs;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnUpdateClick(Sender: TObject);
    procedure btnPathUpdateClick(Sender: TObject);
    procedure Timer1Timer(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
  private
    { Private declarations }
  public
    { Public declarations }
    reg: TdbReg;
    ErrorLog: TStringList;
    TimeStamp: TDateTime;
    ErrCount: Integer;
    Countdown: Integer;
    TempLog: TStringList;
  end;

var
  MMDBFrm: TMMDBFrm;

implementation

{$R *.dfm}

{ TMMDBFrm }


(***************************************************************************
btnUpdate(): Allow for manually started updates
*******************************************************************************)
procedure TMMDBFrm.btnUpdateClick(Sender: TObject);
begin
  UpdateDocs;
end;


(***************************************************************************
btnPathUpdate(): Save changes to registry
*******************************************************************************)
procedure TMMDBFrm.btnPathUpdateClick(Sender: TObject);
begin
  reg.LogFilePath := edLogFilePath.Text;
  reg.DocCtrlPath := edDocCtrlPath.Text;
  reg.KitsPath := edKitsPath.Text;
  reg.NetCDSPath := edNetCDSPath.Text;
  reg.LocalCDSPath := edLocalCDSPath.Text;
end;

(***************************************************************************
FormClose(): Exectued  when the main form is closed
*******************************************************************************)
procedure TMMDBFrm.FormClose(Sender: TObject; var Action: TCloseAction);
var
  tmp: string;
begin
  reg.LogFilePath := edLogFilePath.Text;
  reg.DocCtrlPath := edDocCtrlPath.Text;
  reg.KitsPath := edKitsPath.Text;
  reg.NetCDSPath := edNetCDSPath.Text;
  reg.LocalCDSPath := edLocalCDSPath.Text;
  try
    reg.RegSave;
  except
    Memo1.Lines.Add(': Unable to save settings to registry.');
    ErrorLog.Add(DateTimeToStr(Now) +': Unable to save settings to registry.');
    inc(ErrCount);
  end;
  try
    if ErrCount > 0 then
      begin
        Memo1.Lines.Add('Update failed due to errors above.');
        ErrorLog.Add(DateTimeToStr(Now) + ': Update failed due to errors above.')
      end
      else
        begin
          Memo1.Lines.Add('Update completed successfully.');
          ErrorLog.Add(DateTimeToStr(Now) + ': Update completed successfully.');
        end;
    ErrorLog.Add('***********MMDB Update concluded '+ DateTimeToStr(Now) +
                  ' **********' + #13);
    ErrorLog.SaveToFile(reg.LogFilePath+'\MMDBUpdateLog.log');
  finally
    reg.Free;
    ErrorLog.Free;
  end;
end;

(***************************************************************************
FormCreate(): Exectued at start up when them main form is created.
*******************************************************************************)
procedure TMMDBFrm.FormCreate(Sender: TObject);
begin
  TempLog := TStringList.Create;
  ErrorLog := TStringList.Create;
  try
    reg := TdbReg.Create;
    reg.RegLoad;
    edLogFilePath.Text := reg.LogFilePath;
    edDocCtrlPath.Text := reg.DocCtrlPath;
    edKitsPath.Text := reg.KitsPath;
    edNetCDSPath.Text := reg.NetCDSPath;
    edLocalCDSPath.Text := reg.LocalCDSPath;
    ErrorLog.Add('***********MMDB Update started '+ DateTimeToStr(Now) +
                  ' **********');
  except
    Memo1.Lines.Add('Unable to load regisitry values.');
    ErrorLog.Add(DateTimeToStr(Now)+ 'Unable to load regisitry values.');
    inc(ErrCount);
    //Application.Terminate;
  end;
  if FileExists(reg.NetCDSPath+'\MMDBV2_1DataFile.cds', True) then
    begin
      try
        if FileExists(reg.LocalCDSPath+'\MMDBV2_1DataFile.cds', False) then
          begin
            dbDataSet.LoadFromFile(reg.LocalCDSPath+'\MMDBV2_1DataFile.cds');
            dbDataSet.FileName := reg.LocalCDSPath+'\MMDBV2_1DataFile.cds';
          end
          else
            begin
              try
                dbDataSet.FileName := reg.LocalCDSPath+'\MMDBV2_1DataFile.cds';
                dbDataSet.CreateDataSet;
              except
                ErrorLog.Add(DateTimeToStr(Now)+ 'Unable to find local CDS file.');
                Memo1.Lines.Add('Unable to find local CDS file.')
              end;
            end;
      except
        ErrorLog.Add(DateTimeToStr(Now)+ 'Unable to load local CDS file.');
        Memo1.Lines.Add('Unable to load local CDS file.')
        //Application.Terminate;
      end;
    end
    else   //Need to build a new network file, as it does not exist
      begin
        ErrorLog.Add(DateTimeToStr(Now)+ 'Unable to find network CDS file. Creating new file.');
        Memo1.Lines.Add('Unable to find network CDS file. Creating new file.');
        try
          dbDataSet.FileName := reg.NetCDSPath+'\MMDBV2_1DataFile.cds';
          dbDataSet.CreateDataSet;
          if FileExists(reg.LocalCDSPath+'\MMDBV2_1DataFile.cds', False) then
            begin
              dbDataSet.LoadFromFile(reg.LocalCDSPath+'\MMDBV2_1DataFile.cds');
              dbDataSet.FileName := reg.LocalCDSPath+'\MMDBV2_1DataFile.cds';
            end
            else
              begin
                dbDataSet.FileName := reg.LocalCDSPath+'\MMDBV2_1DataFile.cds';
                dbDataSet.CreateDataSet;
                ErrorLog.Add(DateTimeToStr(Now)+ 'Unable to find local CDS file.');
                Memo1.Lines.Add('Unable to find local CDS file.')
              end;
        except
          ErrorLog.Add(DateTimeToStr(Now)+ 'Unable to create or load CDS file.');
          Memo1.Lines.Add('Unable to create or load CDS file.')
        //Application.Terminate;
        end;
      end;
    CountDown := 6;
    Timer1.Enabled := True;
end;

procedure TMMDBFrm.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Timer1.Enabled then
    begin
      Timer1.Enabled := False;
      lblCountDown.Visible := False;
    end;
end;

procedure TMMDBFrm.Timer1Timer(Sender: TObject);
begin
  if CountDown <= 0 then
    begin
      Timer1.Enabled := False;
      lblCountDown.Visible := False;
      UpdateDocs;
    end
    else
      begin;
        dec(CountDown);
        lblCountDown.Caption := 'AUTOSTART IN: '+IntToStr(CountDown) + ' SECONDS' +
                                  #13+'Press any key to cancel.';
      end;
end;

(***************************************************************************
UpdateDocs:     This procedure performs the update to the document db file. Uses
                SQL ADOQuery to Oracle to pull the most recent list of docs, then
                pulls actual doc paths from doc control and finds matches. Data is
                then placed in client data set file for usage by user.
*******************************************************************************)
procedure TMMDBFrm.UpdateDocs;
var
  DocList: TStringList;
  errLog, tmpVal: string;
  i,tmp: Integer;
  str: string;
  docIdx: TDocType;
  Rec: TSearchRec;

begin
  dsDocList.CreateDataSet;      //create a temp data set to use in this function
  dsDocList.Open;
  try
    OraSession.Connect;   //try to connect to server
    if OraSession.Connected then
      Memo1.Lines.Add('Connection to Oracle opened.');
  except
    Memo1.Lines.Add('Unable to connect to Oracle database.');
    ErrorLog.Add(DateTimeToStr(Now)+ ' :Unable to connect to Oracle database.');
    inc(ErrCount);
    exit;
  end;
  try
    if not dbDataSet.Active then
      dbDataSet.Open;
    Memo1.Lines.Add('dbDataSet successfully opened.');
    dbDataSet.Filtered := False;
  except
    Memo1.Lines.Add('Unable to open dbDataSet.');
    ErrorLog.Add(DateTimeToStr(Now)+ ' :Unable to open dbDataSet.');
    inc(ErrCount);
    exit;
  end;
  if dbDataSet.RecordCount >0 then
    dbDataSet.EmptyDataSet;
  for docIdx := eCRA to eDEC do //Low(TDocType) to High(TDocType) do
    begin
      Application.ProcessMessages;
      //First, get the most current doc list from Oracle for each document type
       if not OraSession.Connected then
        begin
         Memo1.Lines.Add('OraSession not connected to Oracle.');
         ErrorLog.Add(DateTimeToStr(Now)+ ': OraSession not connected to Oracle.');
         inc(ErrCount);
         exit;
        end;
      try
        Memo1.Lines.Add('Updating '+ ExtractDocType(docIdx) +
                                   ' doc list from Oracle...' ) ;

        OraQuery.SQL.Clear;  //Make sure previous string is not present.
        //Prepare SQL query for doc num, rev, and title, cycling through
        //each doc type.
        if docIdx  in [ePRO, eREP, eTESTER, eTF] then//<> eKIT then
          begin
            OraQuery.SQL.Add('SELECT DISTINCT APPS.MM_DOCUMENTS.DOC_NUMBER,');
            OraQuery.SQL.Add('APPS.MM_DOCUMENTS.TITLE, MAX(APPS.MM_DOCUMENTS.REV) ');
            OraQuery.SQL.Add('OVER (PARTITION BY DOC_NUMBER) AS MREV' );
            OraQuery.SQL.Add('FROM APPS.MM_DOCUMENTS WHERE' + GetDocSql(docIdx));
            OraQuery.SQL.Add(' ORDER BY DOC_NUMBER');
          end
          else
          if docIdx in [eBLK, eCRA, eCMS, eDEC, eDWG, eFix, eFMEA, eFORM, eGPS, eKIT, eMPS, ePPS,
                             ePPS, eQAP, eQSP, eSP, eSTP, eTBL, eTEC, eWS] then
            begin
              OraQuery.SQL.Add('select distinct mtl_system_items.segment1 AS DOC_NUMBER , mtl_system_items.description AS TITLE, ');
              OraQuery.SQL.Add('mtl_item_revisions.inventory_item_id, ');
              OraQuery.SQL.Add('max(mtl_item_revisions.revision) over (partition by (mtl_item_revisions.inventory_item_id)) as MREV ');
              OraQuery.SQL.Add('from mtl_system_items inner join mtl_item_revisions on ');
              OraQuery.SQL.Add('(mtl_system_items.inventory_item_id = mtl_item_revisions.inventory_item_id)');
              OraQuery.SQL.Add('where' + GetDocSql(docIdx));
              OraQuery.SQL.Add(' ORDER BY DOC_NUMBER');
            end;
         str := '';
         for tmp := 0 to OraQuery.SQL.Count-1 do
         str := str+ OraQuery.SQL[tmp] + ' '+#13;
         Memo1.Lines.Add('SQL: '+str);
         OraQuery.Open;  //execute the query.
         Memo1.Lines.Add('Successfully executed Query.');
         Memo1.Lines.Add('Records found: '+OraQuery.RecordCount.ToString);
         Memo1.Lines.Add('Transferring Query to client data set... ');
       OraQuery.First;  //ensure at first record
       if docIdx <> eKIT then
         begin       //Document not a kit, use standard Table field names
          while not OraQuery.Eof do
            begin
              if OraQuery.FieldByName('TITLE').AsString <> '' then
                begin
                  str := copy(OraQuery.FieldByName('DOC_NUMBER').AsString, 0,Length(OraQuery.FieldByName('DOC_NUMBER').AsString)-4);
                  dbDataSet.AppendRecord([
                  str,
                  OraQuery.FieldByName('MREV').AsString,
                  OraQuery.FieldByName('TITLE').AsString, '', '',   //dbcShortcut and dbcPath fields leftblank
                  str + OraQuery.FieldByName('MREV').AsString,      //combining doc num and rev here.
                  ExtractDocType(docIdx)]);
                end;
              OraQuery.Next;
              if OraQuery.RecNo mod 5 = 0 then
                Memo1.Lines[Memo1.Lines.count-1] := Memo1.Lines[Memo1.Lines.count-1] + '.';
              TempLog.Add('Doc Num: '+ dbDataSet.FieldByName('dbcDocNum').AsString +
                          ' Doc Rev: ' + dbDataSet.FieldByName('dbcDocRev').AsString +
                          ' Doc Title: '+ dbDataSet.FieldByName('dbcDocTitle').AsString);    //combining doc num and rev here.

            end;
        end
        else    //Document is a Kit, Field Names are different
          begin
            while not OraQuery.Eof do
            begin
              if OraQuery.FieldByName('DESCRIPTION').AsString <> '' then
                begin
                  str := copy(OraQuery.FieldByName('SEGMENT1').AsString, 0, Length(OraQuery.FieldByName(
                     'SEGMENT1').AsString)-4);
                  dbDataSet.AppendRecord([
                  str,    //don't want to copy "D" at 1st of file name.
                  OraQuery.FieldByName('REVISION').AsString,
                  OraQuery.FieldByName('DESCRIPTION').AsString, '', '', '',   //dbcShortcut, dbcPath fields and dbcDocNumRev leftblank
                  ExtractDocType(docIdx)]);
                end;
              OraQuery.Next;
            end;   //end while
            (*The following code gets rid of the "-" character in the doc number*)
            dbDataSet.Filter :='dbcDocType = ''DK''';
            dbDataSet.Filtered  := True;
            dbDataSet.First;
            while not dbDataSet.Eof do
              begin
                tmpVal := copy(dbDataSet.FieldByName('dbcDocNum').AsString,2,3) +
                          copy(dbDataSet.FieldByName('dbcDocNum').AsString,6,
                          length(dbDataSet.FieldByName('dbcDocNum').AsString));
                dbDataSet.Edit;   //put into edit mode.
                dbDataSet['dbcDocNum'] := tmpVal;
                dbDataSet['dbcDocNumRev'] := tmpVal;  //Rev not recorded in DC Kits names.
                if dbDataSet.RecNo mod 100 = 0 then
                Memo1.Lines[Memo1.Lines.count-1] := Memo1.Lines[Memo1.Lines.count-1] + '.';
                TempLog.Add(dbDataSet.FieldByName('dbcDocNum').AsString);
                dbDataSet.Next;
              end;

          end;  //end else and TRY block.
      except
        Memo1.Lines.Add('Irrecoverable Error encountered while executing Oracle query.');
        ErrorLog.Add(DateTimeToStr(Now)+ ': Irrecoverable Error encountered while executing Oracle query.');
        inc(ErrCount);
        exit;
      end;
      //Now that the client ds contains the proper doc list for the current doc type,
      //obtain a list of doc paths from the doc control drive, and match them up with
      // the docs in the data set. Remove any superfluous records.
      try
        if docIdx <> eKIT then
           GetDocPaths(docIdx, dsDocList, reg.DocCtrlPath, Memo1)
           else
             GetDocPaths(docIdx, dsDocList, reg.KitsPath, Memo1);    //only kits have different root.
        Memo1.Lines.Add('Document list contains '+IntToStr(dsDocList.RecordCount) +
                        ' '+ ExtractDocType(docIdx)+' documents.');
      except
        Memo1.Lines.Add('Error encountered while reading from Doc Control.');
        ErrorLog.Add(DateTimeToStr(Now)+ ': Error encountered while reading from Doc Control.');
        inc(ErrCount);
      end;
    MatchDocToPath(dbDataSet, dsDocList, Memo1, docIdx);
    try
      dbDataSet.SaveToFile(reg.LocalCDSPath+'\MMDBV2_1DataFile.cds');
      Memo1.Lines.Add('Adding '+ ExtractDocType(docIdx)+ ' to local data file.');
    except
     Memo1.Lines.Add('Unable to save local data file.');
     ErrorLog.Add(DateTimeToStr(Now)+ ': Unable to save local data File.');
     inc(ErrCount);
    end;
  end;  //end for loop
  if ErrCount = 0 then
    try
        dbDataSet.SaveToFile(reg.NetCDSPath + '\MMDBV2_1DataFile.cds');
    except
        Memo1.Lines.Add(':Unable to save data file to network location '+ reg.NetCDSPath);
        ErrorLog.Add(DateTimeToStr(Now)+ ': Unable to save data file to network location '+ reg.NetCDSPath);
        inc(ErrCount);
    end;
  if ErrCount = 0 then
    begin
      Memo1.Lines.Add('Document list complete.');
      Memo1.Lines.Add('Total Doc Count: '+IntToStr(dbDataSet.RecordCount));
      ErrorLog.Add(DateTimeToStr(Now)+ ': Document list complete.');
      ErrorLog.Add(DateTimeToStr(Now)+ ': Total Doc Count: '+IntToStr(dbDataSet.RecordCount));
    end;
  DocList.Free;
  ADOConnection.Close;    //Can now disconnect from Oracle.
  //dbDataSet.Filtered := True; //reapply filter.
  try
    try
      TempLog.SaveToFile('c:\temp\mmdblogtemp.txt');
    except
      ShowMessage('error creating temp log file.');
    end;
  finally
    if TempLog <> nil then
      TempLog.Free;
  end;
 // MMDBFrm.Close;
end;


end.
